package com.wudl.ums.utils;



import com.wudl.ums.entity.MetaIndex;
import com.wudl.ums.entity.Source;
import com.wudl.ums.entity.TableAttributeEntity;
import org.bouncycastle.asn1.cms.MetaData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.lang.Nullable;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;
import java.util.regex.Pattern;

/**
 * @ClassName : DBUtil
 * @Description : 数据库工具类
 * @Author :wudl
 * @Date: 2020-04-12 21:46
 */
public class DBUtil {
    private static Logger log = LoggerFactory.getLogger(DBUtil.class);
    private static final int DB_CONNECTION_TIMEOUTS_SECONDS = 1;
    private static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 关闭连接
     * @param ps
     * @param rs
     * @param con
     */
    public static void closeLink(PreparedStatement ps,Statement statement,ResultSet rs,Connection con){
        Optional.ofNullable(ps).ifPresent(p-> {
            try {
                p.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
        Optional.ofNullable(statement).ifPresent(s-> {
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });

        Optional.ofNullable(rs).ifPresent(r-> {
            try {
                r.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });

        Optional.ofNullable(con).ifPresent(c-> {
            try {
                c.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }

    /**
     * 获得数据库连接
     *
     * @param config
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */

    public static Connection getConnection(Source config) throws ClassNotFoundException, SQLException {
        DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
        DBType dbType = DBType.valueOf(config.getDbType());
        Class.forName(dbType.getDriverClass());
        String url = getConnectionURL(config);
        if (dbType == DBType.Oracle) {
            Connection connection;
            try {
                connection = DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
            } catch (Exception e) {
                String oracle = String.format(DBType.OracleServiceName.getConnectionUrlPattern(), config.getDbUrl(), config.getListenPort(),
                        config.getDbName());
                connection = DriverManager.getConnection(oracle, config.getUserName(), config.getUserPwd());
            }
            return connection;
        } else {
            return DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
        }
    }

    /**
     * 获得数据库连接URL
     *
     * @param dbConfig
     * @return
     * @throws ClassNotFoundException
     */
    public static String getConnectionURL(Source dbConfig) throws ClassNotFoundException {
        DBType dbType = DBType.valueOf(dbConfig.getDbType());
        String connectionRUL = String.format(dbType.getConnectionUrlPattern(), dbConfig.getDbUrl(), dbConfig.getListenPort(),
                dbConfig.getDbName(), "UTF-8");
        return connectionRUL;
    }

    /**
     * 获得数据库的表名
     *
     * @param config
     * @return
     * @throws Exception
     */
    public static Map<String, Object> getTableNames(Source config, String[] mateTypes) throws Exception {
        Connection conn = getConnection(config);
        List<String> tables = new ArrayList<>();
        Map<String, Object> map = new HashMap<String, Object>();
        ResultSet rs;
        if (config.getDbType().equalsIgnoreCase(DbConstant.SQL_SERVER)) {
            // 如果是sqlserver数据库通过查询获得所有表跟视图
            String sql = "select name from sysobjects  where xtype='u' or xtype='v' ";
            rs = conn.createStatement().executeQuery(sql);
            while (rs.next()) {
                tables.add(rs.getString("name"));
            }
        } else {
            // 如果非sqlserver类型的数据库通过JDBC获得所有表跟视图
            DatabaseMetaData md = conn.getMetaData();
//            String[] types = {"TABLE", "VIEW"};
            if (config.getDbType().equalsIgnoreCase(DbConstant.POSTGRE_SQL)) {
                rs = md.getTables(null, null, null, mateTypes);

            } else {
                String catalog = conn.getCatalog() == null ? null : conn.getCatalog();

                rs = md.getTables(catalog, config.getUserName().toUpperCase(), "%%", mateTypes);

            }
            while (rs.next()) {
                tables.add(rs.getString(3));
//                String tableName = rs.getString("TABLE_NAME"); // 表名
//                String tableType = rs.getString("TABLE_TYPE"); // 表类型
//                String remarks = rs.getString("REMARKS"); // 表备注
//                System.out.println("###" + rs.getString("REMARKS"));
//                System.out.println("@@@" + rs.getString("TABLE_CATALOG"));
//                System.out.println("&&&&" + rs.getString("TABLE_COMMENT"));

                //   System.out.println(tableName + "-" + tableType + "-@@@@" + rs.getString(1)+"---*****---"+rs.getString(2));
            }
        }
        map.put(mateTypes[0], tables);
        return map;
    }

//    /**
//     * * 获得指定表的属性
//     * *
//     * * @param config
//     * * @param tableName
//     * * @return
//     * * @throws Exception
//     */
//    public static TableContent getTableAttribute(Source config, String tableName) throws Exception {
//        Connection conn = getConnection(config);
//        TableContent content = new TableContent();
//        ResultSet rs;
//        DatabaseMetaData md = conn.getMetaData();
//        String[] types = {"TABLE", "VIEW"};
//        if (config.getDbType().equalsIgnoreCase(DbConstant.POSTGRE_SQL)) {
//            rs = md.getTables(null, null, tableName, types);
//        } else {
//            String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
//            rs = md.getTables(catalog, config.getUserName().toUpperCase(), tableName, types);
//        }
//        if (rs.next()) {
//            try {
//                content.setTableCat(rs.getString("TABLE_CAT"));
//                content.setTableSchem(rs.getString("TABLE_SCHEM"));
//                content.setTableName(rs.getString("TABLE_NAME"));
//                content.setTableType(rs.getString("TABLE_TYPE"));
//                content.setRemarks(rs.getString("REMARKS"));
//                content.setTypeCat(rs.getString("TYPE_CAT"));
//                content.setTypeSchem(rs.getString("TYPE_SCHEM"));
//                content.setTypeName(rs.getString("TYPE_NAME"));
//                content.setSelfReferencingColName(rs.getString("SELF_REFERENCING_COL_NAME"));
//                content.setRefGeneration(rs.getString("REF_GENERATION"));
//            } catch (Exception e) {
//                log.error("部分属性获取失败:", e.getMessage());
//            }
//        }
//        return content;
//    }

    /**
     * 获取表的列属性
     *
     * @param config    数据库配置文件
     * @param tableName 表名
     * @return
     * @throws Exception
     */
    public static List<TableAttributeEntity> getTableColumns(Source config, String tableName) throws Exception {
        Connection conn = getConnection(config);
        DatabaseMetaData md = conn.getMetaData();

        ResultSet rs = null;
        if (config.getDbType().equalsIgnoreCase(DbConstant.MYSQL)) {
            rs = md.getColumns(conn.getCatalog(), "%%", tableName, "%%");
        } else {
            rs = md.getColumns(null, null, tableName, null);
        }
        Map<String, TableAttributeEntity> columnMap = new HashMap<>();
        while (rs.next()) {
            TableAttributeEntity attr = new TableAttributeEntity();
            try {
                attr.setTdColumnName(rs.getString("COLUMN_NAME"));
                attr.setTdJdbcType(rs.getString("TYPE_NAME"));
//                attr.setTdJavaType(JavaType.jdbcTypeToJavaType(rs.getString("TYPE_NAME")));
                attr.setColumnDef(rs.getString("COLUMN_DEF"));
                attr.setRemarks(rs.getString("REMARKS"));
                attr.setColumnSize(rs.getInt("COLUMN_SIZE"));
                attr.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
                attr.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
                attr.setNullable(rs.getInt("NULLABLE") == 1 ? true : false);
                columnMap.put(rs.getString("COLUMN_NAME"), attr);
            } catch (Exception e) {
                log.error("部分属性获取失败:", e);
            }
        }
        if (columnMap.size() == 0) {
            throw new NullPointerException("从表中获取字段失败!获取不到任何字段!");
        }
        ArrayList<TableAttributeEntity> result = new ArrayList<>(columnMap.values());
//        Collections.sort(result);
        return result;
    }

    /**
     * 获得主键名称
     *
     * @param config
     * @param
     * @return
     * @throws Exception
     */
    public static String getTablePrimaryKey(Source config, String teDataType) throws Exception {
        Connection conn = getConnection(config);
        DatabaseMetaData md = conn.getMetaData();
        ResultSet rs = null;
        if (config.getDbType().equalsIgnoreCase(DbConstant.MYSQL)) {
            rs = md.getPrimaryKeys(conn.getCatalog(), conn.getSchema(), teDataType);
        } else {
            rs = md.getPrimaryKeys(null, null, teDataType);
        }
        while (rs.next()) {
            return rs.getString("COLUMN_NAME");
        }
        return null;
    }

    /**
     * 执行数据库插入操作
     *
     * @param valueMap  插入数据表中key为列名和value为列对应的值的Map对象
     * @param tableName 要插入的数据库的表名
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public static int insert(Connection conn, String tableName, Map<String, Object> valueMap) throws SQLException {

        /**获取数据库插入的Map的键值对的值**/
        Set<String> keySet = valueMap.keySet();
        Iterator<String> iterator = keySet.iterator();
        /**要插入的字段sql，其实就是用key拼起来的**/
        StringBuilder columnSql = new StringBuilder();
        /**要插入的字段值，其实就是？**/
        StringBuilder unknownMarkSql = new StringBuilder();
        Object[] bindArgs = new Object[valueMap.size()];
        int i = 0;
        while (iterator.hasNext()) {
            String key = iterator.next();
            columnSql.append(i == 0 ? "" : ",");
            columnSql.append(key);

            unknownMarkSql.append(i == 0 ? "" : ",");
            unknownMarkSql.append("?");
            bindArgs[i] = valueMap.get(key);
            i++;
        }
        /**开始拼插入的sql语句**/
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ");
        sql.append(tableName);
        sql.append(" (");
        sql.append(columnSql);
        sql.append(" )  VALUES (");
        sql.append(unknownMarkSql);
        sql.append(" )");
        return executeUpdate(conn, sql.toString(), bindArgs);
    }

    /**
     * 执行数据库插入操作
     *
     * @param datas     插入数据表中key为列名和value为列对应的值的Map对象的List集合
     * @param tableName 要插入的数据库的表名
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public static int insertAll(Source config, String tableName, List<Map<String, Object>> datas) throws SQLException, ClassNotFoundException {
        /**影响的行数**/
        int affectRowCount = -1;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            /**从数据库连接池中获取数据库连接**/
            connection = getConnection(config);

            Map<String, Object> valueMap = datas.get(0);
            /**获取数据库插入的Map的键值对的值**/
            Set<String> keySet = valueMap.keySet();
            Iterator<String> iterator = keySet.iterator();
            /**要插入的字段sql，其实就是用key拼起来的**/
            StringBuilder columnSql = new StringBuilder();
            /**要插入的字段值，其实就是？**/
            StringBuilder unknownMarkSql = new StringBuilder();
            Object[] keys = new Object[valueMap.size()];
            int i = 0;
            while (iterator.hasNext()) {
                String key = iterator.next();
                keys[i] = key;
                columnSql.append(i == 0 ? "" : ",");
                columnSql.append(key);

                unknownMarkSql.append(i == 0 ? "" : ",");
                unknownMarkSql.append("?");
                i++;
            }
            /**开始拼插入的sql语句**/
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ");
            sql.append(tableName);
            sql.append(" (");
            sql.append(columnSql);
            sql.append(" )  VALUES (");
            sql.append(unknownMarkSql);
            sql.append(" )");

            /**执行SQL预编译**/
            preparedStatement = connection.prepareStatement(sql.toString());
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            System.out.println(sql.toString());
            for (int j = 0; j < datas.size(); j++) {
                for (int k = 0; k < keys.length; k++) {
                    preparedStatement.setObject(k + 1, datas.get(j).get(keys[k]));
                }
                preparedStatement.addBatch();
            }
            int[] arr = preparedStatement.executeBatch();
            connection.commit();
            affectRowCount = arr.length;
            System.out.println("成功了插入了" + affectRowCount + "行");
        } catch (Exception e) {
            if (connection != null) {
                connection.rollback();
            }
            throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return affectRowCount;
    }

    /**
     * 执行更新操作
     *
     * @param tableName 表名
     * @param valueMap  要更改的值
     * @param whereMap  条件
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public static int update(Connection connection, String tableName, Map<String, Object> valueMap, Map<String, Object> whereMap) throws SQLException {
        /**获取数据库插入的Map的键值对的值**/
        Set<String> keySet = valueMap.keySet();
        Iterator<String> iterator = keySet.iterator();
        /**开始拼插入的sql语句**/
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ");
        sql.append(tableName);
        sql.append(" SET ");

        /**要更改的的字段sql，其实就是用key拼起来的**/
        StringBuilder columnSql = new StringBuilder();
        int i = 0;
        List<Object> objects = new ArrayList<>();
        while (iterator.hasNext()) {
            String key = iterator.next();
            columnSql.append(i == 0 ? "" : ",");
            columnSql.append(key + " = ? ");
            objects.add(valueMap.get(key));
            i++;
        }
        sql.append(columnSql);

        /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/
        StringBuilder whereSql = new StringBuilder();
        int j = 0;
        if (whereMap != null && whereMap.size() > 0) {
            whereSql.append(" WHERE ");
            iterator = whereMap.keySet().iterator();
            while (iterator.hasNext()) {
                String key = iterator.next();
                whereSql.append(j == 0 ? "" : " AND ");
                whereSql.append(key + " = ? ");
                objects.add(whereMap.get(key));
                j++;
            }
            sql.append(whereSql);
        }
        return executeUpdate(connection, sql.toString(), objects.toArray());
    }

    /**
     * 执行删除操作
     *
     * @param tableName 要删除的表名
     * @param whereMap  删除的条件
     * @return 影响的行数
     * @throws SQLException SQL执行异常
     */
    public static int delete(Connection conn, String tableName, Map<String, Object> whereMap) throws SQLException {
        /**准备删除的sql语句**/
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ");
        sql.append(tableName);

        /**更新的条件:要更改的的字段sql，其实就是用key拼起来的**/
        StringBuilder whereSql = new StringBuilder();
        Object[] bindArgs = null;
        if (whereMap != null && whereMap.size() > 0) {
            bindArgs = new Object[whereMap.size()];
            whereSql.append(" WHERE ");
            /**获取数据库插入的Map的键值对的值**/
            Set<String> keySet = whereMap.keySet();
            Iterator<String> iterator = keySet.iterator();
            int i = 0;
            while (iterator.hasNext()) {
                String key = iterator.next();
                whereSql.append(i == 0 ? "" : " AND ");
                whereSql.append(key + " = ? ");
                bindArgs[i] = whereMap.get(key);
                i++;
            }
            sql.append(whereSql);
        }
        return executeUpdate(conn, sql.toString(), bindArgs);
    }

    /**
     * 可以执行新增，修改，删除
     *
     * @param sql      sql语句
     * @param bindArgs 绑定参数
     * @return 影响的行数
     * @throws SQLException SQL异常
     */
    public static int executeUpdate(Connection conn, String sql, Object[] bindArgs) throws SQLException {
        /**影响的行数**/
        int affectRowCount = -1;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            /**从数据库连接池中获取数据库连接**/
            connection = conn;
            /**执行SQL预编译**/
            preparedStatement = connection.prepareStatement(sql.toString());
            /**设置不自动提交，以便于在出现异常的时候数据库回滚**/
            connection.setAutoCommit(false);
            // System.out.println(getExecSQL(sql, bindArgs));
            if (bindArgs != null) {
                /**绑定参数设置sql占位符中的值**/
                for (int i = 0; i < bindArgs.length; i++) {
                    preparedStatement.setObject(i + 1, bindArgs[i]);
                }
            }
            /**执行sql**/
            affectRowCount = preparedStatement.executeUpdate();
            connection.commit();
            String operate;
            if (sql.toUpperCase().indexOf("DELETE FROM") != -1) {
                operate = "删除";
            } else if (sql.toUpperCase().indexOf("INSERT INTO") != -1) {
                operate = "新增";
            } else {
                operate = "修改";
            }
            System.out.println("成功" + operate + "了" + affectRowCount + "行");
        } catch (Exception e) {
            if (connection != null) {
                connection.rollback();
            }
            e.printStackTrace();
            throw e;
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return affectRowCount;
    }

    /**
     * 通过sql查询数据,
     * 慎用，会有sql注入问题
     *
     * @param sql
     * @return 查询的数据集合
     */
    public static List<Map<String, Object>> query(Connection conn, String sql) throws Exception {
        return executeQuery(conn, sql, null);
    }

    /**
     * 执行sql通过 Map<String, Object>限定查询条件查询
     *
     * @param tableName 表名
     * @param whereMap  where条件
     * @return List<Map < String, Object>>
     * @throws SQLException
     */
    public static List<Map<String, Object>> query(Connection connection, String tableName,
                                                  Map<String, Object> whereMap) throws Exception {
        String whereClause = "";
        Object[] whereArgs = null;
        if (whereMap != null && whereMap.size() > 0) {
            Iterator<String> iterator = whereMap.keySet().iterator();
            whereArgs = new Object[whereMap.size()];
            int i = 0;
            while (iterator.hasNext()) {
                String key = iterator.next();
                whereClause += (i == 0 ? "" : " AND ");
                whereClause += (key + " = ? ");
                whereArgs[i] = whereMap.get(key);
                i++;
            }
        }
        return query(connection, tableName, false, null, whereClause, whereArgs, null, null, null, null);
    }

    /**
     * 执行sql条件参数绑定形式的查询
     *
     * @param tableName   表名
     * @param whereClause where条件的sql
     * @param whereArgs   where条件中占位符中的值
     * @return List<Map < String, Object>>
     * @throws SQLException
     */
    public static List<Map<String, Object>> query(Connection conn, String tableName,
                                                  String whereClause,
                                                  String[] whereArgs) throws SQLException {
        return query(conn, tableName, false, null, whereClause, whereArgs, null, null, null, null);
    }

    /**
     * 执行全部结构的sql查询
     *
     * @param tableName     表名
     * @param distinct      去重
     * @param columns       要查询的列名
     * @param selection     where条件
     * @param selectionArgs where条件中占位符中的值
     * @param groupBy       分组
     * @param having        筛选
     * @param orderBy       排序
     * @param limit         分页
     * @return List<Map < String, Object>>
     * @throws SQLException
     */
    public static List<Map<String, Object>> query(Connection conn, String tableName,
                                                  boolean distinct,
                                                  String[] columns,
                                                  String selection,
                                                  Object[] selectionArgs,
                                                  String groupBy,
                                                  String having,
                                                  String orderBy,
                                                  String limit) throws SQLException {
        String sql = buildQueryString(distinct, tableName, columns, selection, groupBy, having, orderBy, limit);
        return executeQuery(conn, sql, selectionArgs);

    }

    /**
     * 执行查询
     *
     * @param sql      要执行的sql语句
     * @param bindArgs 绑定的参数
     * @return List<Map < String, Object>>结果集对象
     * @throws SQLException SQL执行异常
     */
    public static List<Map<String, Object>> executeQuery(Connection connection, String sql, Object[] bindArgs) throws SQLException {
        List<Map<String, Object>> datas = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            /**获取数据库连接池中的连接**/
            preparedStatement = connection.prepareStatement(sql);
            if (bindArgs != null) {
                /**设置sql占位符中的值**/
                for (int i = 0; i < bindArgs.length; i++) {
                    preparedStatement.setObject(i + 1, bindArgs[i]);
                }
            }
            System.out.println(getExecSQL(sql, bindArgs));
            /**执行sql语句，获取结果集**/
            resultSet = preparedStatement.executeQuery();
            datas = getDatas(resultSet);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
        return datas;
    }


    /**
     * 将结果集对象封装成List<Map<String, Object>> 对象
     *
     * @param resultSet 结果多想
     * @return 结果的封装
     * @throws SQLException
     */
    private static List<Map<String, Object>> getDatas(ResultSet resultSet) throws SQLException {
        List<Map<String, Object>> datas = new ArrayList<>();
        /**获取结果集的数据结构对象**/
        ResultSetMetaData metaData = resultSet.getMetaData();
        String localMetaData= "";
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            localMetaData += metaData.getColumnLabel(i)+",";
        }

        if(localMetaData.length()>0){
            localMetaData = localMetaData.substring(0,localMetaData.length()-1);
        }
//        LocalSqlMetaData.setLocalSqlMetaData(localMetaData);

        while (resultSet.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                rowMap.put(metaData.getColumnLabel(i), resultSet.getObject(i)==null?"":resultSet.getObject(i).toString());
            }
            datas.add(rowMap);
        }
        for (int i = 0; i < datas.size(); i++) {
            Map<String, Object> map = datas.get(i);
        }
        return datas;
    }


    /**
     * Build an SQL query string from the given clauses.
     *
     * @param distinct true if you want each row to be unique, false otherwise.
     * @param tables   The table names to compile the query against.
     * @param columns  A list of which columns to return. Passing null will
     *                 return all columns, which is discouraged to prevent reading
     *                 data from storage that isn't going to be used.
     * @param where    A filter declaring which rows to return, formatted as an SQL
     *                 WHERE clause (excluding the WHERE itself). Passing null will
     *                 return all rows for the given URL.
     * @param groupBy  A filter declaring how to group rows, formatted as an SQL
     *                 GROUP BY clause (excluding the GROUP BY itself). Passing null
     *                 will cause the rows to not be grouped.
     * @param having   A filter declare which row groups to include in the cursor,
     *                 if row grouping is being used, formatted as an SQL HAVING
     *                 clause (excluding the HAVING itself). Passing null will cause
     *                 all row groups to be included, and is required when row
     *                 grouping is not being used.
     * @param orderBy  How to order the rows, formatted as an SQL ORDER BY clause
     *                 (excluding the ORDER BY itself). Passing null will use the
     *                 default sort order, which may be unordered.
     * @param limit    Limits the number of rows returned by the query,
     *                 formatted as LIMIT clause. Passing null denotes no LIMIT clause.
     * @return the SQL query string
     */
    private static String buildQueryString(
            boolean distinct, String tables, String[] columns, String where,
            String groupBy, String having, String orderBy, String limit) {
        if (isEmpty(groupBy) && !isEmpty(having)) {
            throw new IllegalArgumentException(
                    "HAVING clauses are only permitted when using a groupBy clause");
        }
        if (!isEmpty(limit) && !sLimitPattern.matcher(limit).matches()) {
            throw new IllegalArgumentException("invalid LIMIT clauses:" + limit);
        }

        StringBuilder query = new StringBuilder(120);

        query.append("SELECT ");
        if (distinct) {
            query.append("DISTINCT ");
        }
        if (columns != null && columns.length != 0) {
            appendColumns(query, columns);
        } else {
            query.append(" * ");
        }
        query.append("FROM ");
        query.append(tables);
        appendClause(query, " WHERE ", where);
        appendClause(query, " GROUP BY ", groupBy);
        appendClause(query, " HAVING ", having);
        appendClause(query, " ORDER BY ", orderBy);
        appendClause(query, " LIMIT ", limit);
        return query.toString();
    }

    /**
     * Add the names that are non-null in columns to s, separating
     * them with commas.
     */
    private static void appendColumns(StringBuilder s, String[] columns) {
        int n = columns.length;

        for (int i = 0; i < n; i++) {
            String column = columns[i];

            if (column != null) {
                if (i > 0) {
                    s.append(", ");
                }
                s.append(column);
            }
        }
        s.append(' ');
    }

    /**
     * addClause
     *
     * @param s      the add StringBuilder
     * @param name   clauseName
     * @param clause clauseSelection
     */
    private static void appendClause(StringBuilder s, String name, String clause) {
        if (!isEmpty(clause)) {
            s.append(name);
            s.append(clause);
        }
    }

    /**
     * Returns true if the string is null or 0-length.
     *
     * @param str the string to be examined
     * @return true if str is null or zero length
     */
    private static boolean isEmpty(@Nullable CharSequence str) {
        if (str == null || str.length() == 0) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * the pattern of limit
     */
    private static final Pattern sLimitPattern =
            Pattern.compile("\\s*\\d+\\s*(,\\s*\\d+\\s*)?");

    /**
     * After the execution of the complete SQL statement, not necessarily the actual implementation of the SQL statement
     *
     * @param sql      SQL statement
     * @param bindArgs Binding parameters
     * @return Replace? SQL statement executed after the
     */
    private static String getExecSQL(String sql, Object[] bindArgs) {
        StringBuilder sb = new StringBuilder(sql);
        if (bindArgs != null && bindArgs.length > 0) {
            int index = 0;
            for (int i = 0; i < bindArgs.length; i++) {
                index = sb.indexOf("?", index);
                sb.replace(index, index + 1, String.valueOf(bindArgs[i]));
            }
        }
        return sb.toString();

    }


    /**
     * 获得该用户下面的所有表
     */
    public static void getAllTableList(Connection conn, String schemaName) {
        try {

            DatabaseMetaData dbMetaData = conn.getMetaData();
            // table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE",
            // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
            String[] types = {"TABLE"};
            ResultSet rs = dbMetaData.getTables(null, schemaName, "%", types);
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME"); // 表名
                String tableType = rs.getString("TABLE_TYPE"); // 表类型
                String remarks = rs.getString("REMARKS"); // 表备注
                System.out.println(tableName + "-" + tableType + "-" + remarks);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String, Object>> queryPrestoTable(Connection con,String sql,Long dbId){
        List<Map<String, Object>> queryList = new ArrayList<>();
        Statement  statement  = null;
        ResultSet resultSet = null;
        try {
            statement = con.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                Map<String, Object> map = new HashMap<>(16);
                map.put("table_name", resultSet.getString("Table"));
                map.put("table_remark", "");
                map.put("table_type","");
                map.put("database_id", dbId);
                map.put("create_time",df.format(new Date()));
                map.put("update_time",df.format(new Date()));
                queryList.add(map);
            }
        } catch (Exception e) {
        } finally {
            closeLink(null,statement,resultSet,con);
        }
        return queryList;
    }

    /**
     * 查询 oracle 或sqlServer 中某个库的表
     * @param con
     * @param sql
     * @param dbId
     * @return
     */
    public static List<Map<String, Object>> queryOracleOrSqlServerTable(Connection con,String sql,Long dbId){
        List<Map<String, Object>> queryList = new ArrayList<>();
        Statement  statement  = null;
        ResultSet resultSet = null;
        try {
            statement = con.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                Map<String, Object> map = new HashMap<>(16);
                map.put("table_name", resultSet.getString("TABLE_NAME"));
                map.put("table_remark", resultSet.getString("TABLE_REMARK"));
                map.put("table_type", resultSet.getString("TABLE_TYPE"));
                map.put("database_id", dbId);
                map.put("create_time",df.format(new Date()));
                map.put("update_time",df.format(new Date()));
                queryList.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeLink(null,statement,resultSet,con);
        }
        return queryList;
    }

    /**
     * 查询MySQL下的表
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static List<Map<String, Object>> queryMysqlTable(Connection conn, String sql, Long dbId, Object... params) {
        //创建一个集合接收
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        //建立连接
        Connection con = conn;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //传入参数
            pstmt = con.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            rs = pstmt.executeQuery();
            int i = 0;
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>(16);
//                list2.add(rs.getString("TABLE_NAME"));
//                MetaData md = new MetaData();
                map.put("table_remark", rs.getString("TABLE_COMMENT"));
                map.put("table_name", rs.getString("TABLE_NAME"));
                map.put("table_type", rs.getString("TABLE_COMMENT"));
                map.put("database_id", dbId);
                map.put("create_time",df.format(new Date()));
                map.put("update_time",df.format(new Date()));
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeLink(pstmt,null,rs,con);
        }
        return list;
    }


    /**
     * 更新 删除 添加
     *
     * @param sql
     * @param
     * @return
     */
//    public static int updateData(Connection connection, String sql, List<MetaData> list) {
//        int res = 0;
//        Connection conn = null;
//        PreparedStatement ps = null;
//        try {
//            System.out.println("更新语句：" + sql + "，参数长度：" + list.size());
//            conn = connection;
//            ps = conn.prepareStatement(sql);
////            putPs(ps, list);
//            for (int i = 0; i <= list.size(); i++) {
//                if (list.get(i).getTableName() == "" || list.get(i).getTableName().equals(" ")) {
//                    ps.setString(i, null);
//                } else {
//                    ps.setString(i + 1, list.get(i).getTableName());
//                }
//                ps.execute();
//            }
//
//        } catch (SQLException e) {
//            System.out.println("更新异常，SQL语句：" + sql);
//            System.out.println("参数列表：");
//            e.printStackTrace();
//        } finally {
//            return res;
//        }
//    }


    public static List<MetaIndex> queryMysqlIndex(Connection conn, String sql) {

     //   String sql = "SELECT INDEX_NAME,TABLE_SCHEMA,TABLE_NAME,NON_UNIQUE,INDEX_TYPE,CARDINALITY,INDEX_COMMENT,COLUMN_NAME FROM information_schema.`STATISTICS` WHERE TABLE_SCHEMA = 'db_common_v1.0.0' AND TABLE_NAME = 'db_monitor'";

        //创建一个集合接收
        List<MetaIndex> list = new ArrayList<>();
        //建立连接
        Connection con = conn;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //传入参数
            pstmt = con.prepareStatement(sql);

            rs = pstmt.executeQuery();
            int i = 0;
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>(16);
                MetaIndex metaIndex = new MetaIndex();
                String table_comment = rs.getString("INDEX_COMMENT");
                System.out.println(table_comment);
                metaIndex.setIndexName(rs.getString("INDEX_NAME"));
                metaIndex.setIndexShortName(rs.getString("TABLE_NAME"));
                String index_comment = rs.getString("INDEX_COMMENT");
                metaIndex.setIsUnique(0);
                metaIndex.setType(rs.getString("INDEX_TYPE"));
                metaIndex.setRemarks(rs.getString("INDEX_COMMENT"));
                metaIndex.setColumns(rs.getString("COLUMN_NAME"));

                map.put("update_time",df.format(new Date()));
                list.add(metaIndex);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeLink(pstmt,null,rs,con);
        }
        return list;
    }


    public static void main(String[] args) throws Exception {

        Source source = new Source();
        source.setDbName("db_common_v1.0.0");
        source.setDbType("MySQL");
        source.setUserName("root");
        source.setUserPwd("123456");
        source.setDbUrl("192.168.1.180");

        source.setListenPort(3306);


        Connection connection = getConnection(source);
        String sql = "SELECT INDEX_NAME,TABLE_SCHEMA,TABLE_NAME,NON_UNIQUE,INDEX_TYPE,CARDINALITY,INDEX_COMMENT,COLUMN_NAME FROM information_schema.`STATISTICS` WHERE TABLE_SCHEMA = 'db_common_v1.0.0' AND TABLE_NAME = 'db_monitor'";
        List<MetaIndex> maps = queryMysqlIndex(connection, sql);
        for (int i = 0; i <maps.size() ; i++) {
            MetaIndex metaIndex = maps.get(i);
            System.out.println(metaIndex.toString());
        }





    }

}
